SQL UPDATE statement

Probleem

Het UPDATE DML statement gelijkt op het ALTER DDL statement. Beiden brengen één of meerdere wijzigingen aan in de database. Daar waar ALTER de structuur van een tabel wijzigt, wijzigt UPDATE de gegevens die in een tabel zitten.

Oplossing

We hebben daarnet de kolom Categorie toegevoegd. Maar de kolom categorie is voor de eerste tien boeken nog niet ingevuld. We kunnen het UPDATE statement gebruiken om de categorieën voor de eerste tien boeken in te vullen.

In zijn meest eenvoudige vorm ziet het UPDATE statement er zo uit:

-- ji
-- 27 november 2012
--
use A88586JefInghelbrecht;
update Boeken set Categorie = 'Metafysica';
select * from Boeken;

Als je meer dan een kolom wil updaten:

-- ji
-- 11 september 2013
-- Bestandsnaam: BoekenUpdateAllRows.sql
use JefInghelbrecht;
update Boeken set Categorie = 'Wetenschap',
   Titel = 'Mijn eerste boek';
select Titel, Categorie from Boeken;

Zonder verdere specificatie zet dat statement de kolom Categorie van alle rijen op Metafysica.

Het zou kunnen dat alle boeken in de categorie Metafysica thuishoren. En in dat geval doet het UPDATE statement precies wat je er van verwacht. De boeken in ons voorbeeld behoren tot verschillende categorieën. We moeten dus in het UPDATE statement specifiëren in welke rij we de kolom Categorie willen updaten. We moeten één bepaalde rij eruit filteren. Dat doen we met de WHERE clausule. We beperken de update tot de boeken waarvan de titel gelijk is aan 'Logicaboek'.

-- ji
-- 27 november 2012
--
use A88586JefInghelbrecht;
update Boeken
   set Categorie = 'Wiskundige logica. Mathematische logica'
   where Titel = 'Logicaboek';

De WHERE clausule bepaalt een voorwaarde, een logische expressie die door de database als waar of vals geëvalueerd wordt. De voorwaarde Id = 1 wordt voor elke rij in de tabel geëvalueerd. Als de evaluatie van de logische expressie voor een bepaalde rij waar oplevert wordt die rij geüpdated. Nog een voorbeeld:

update Boeken
   set Categorie = 'Logica. Epistemologie, kennistheorie.
   Methodiek der logica, kennisleer'
   where Familienaam = 'Beth';

Opgelet!

De vergelijking is in SQL standaard niet hoofdlettergevoelig (meer info).

De volgende query, waarin beth met een kleine letter wordt geschreven, levert hetzelfde resultaat op als de vorige:

update Boeken
   set Categorie = 'Logica. Epistemologie, kennistheorie.
   Methodiek der logica, kennisleer'
   where Familienaam = 'beth';

Sarah vraagt hoe maak je de where van een query hoofdlettergevoelig.

Dat doe je door de collation eigenschap van de kolom tijdelijk te wijzigen:

select Voornaam, Familienaam, Titel
   from Boeken
   where Titel COLLATE Latin1_General_CI_AS = 'logicaboek';

CS betekent Case Sensitive

AS betekent Accent Sensitive

De voorgaande query zal 1 boek retourneren. Als we de titel in kleine letters schrijven zal er geen boek geretourneerd worden.

select Voornaam, Familienaam, Titel
   from Boeken
   where Titel COLLATE Latin1_General_CS_AS = 'logicaboek';

Als je geen rekening wilt houden met accenten vervang je AS door AI (Accent Insensitiv).

Je kan ook meerdere rijen in één keer updaten. Dat doe je door bijvoorbeeld de logische operator OR te gebruiken:

update Boeken
   set Categorie = 'Geschiedenis'
   where Familienaam = 'Braudel' or
      Familienaam = 'Bernard' or
      Familienaam = 'Bloch';

Verder in het handboek leer je meer over logische operatoren. Meerdere kolommen tegelijk kunnen ook gewijzigd worden.

Oefening
-- ji
-- 27 november 2012
-- Bestandsnaam: BoekenUpdateWhere.sql
-- Opgelet! De vergelijking is niet hoofdlettergevoelig
use A88586JefInghelbrecht;

update Boeken
   set Voornaam = 'Geert',
   Familienaam = 'Hoste',
   Uitgeverij = 'De Bezige Olifant'
where Titel = 'De stad van God';

select * from Boeken;

Werken met functies

Algemene informatie over MSSQL functies waaronder ook string-functies: What are the SQL database functions?

concat

In SQL kan je geen strings 'optellen' om strings samen te stellen. Daarvoor gebruik je de concat methode.

Voorbeeld 1

-- TDC
-- 10 September 2014
--
update Boeken set Categorie = 'Wiskunde'
   where Categorie = 'Geschiedenis';
-- Van alle 'Wiskunde' maak je 'Geschiedenis' als er Geschiedenis in een kolom hebt staan.
update Boeken set Categorie = concat('[1]', Categorie)
   where Categorie = 'Wiskunde';

Voorbeeld 2

use ModernWays;
-- update Boeken
--    set Categorie = 'Theologie'
--    where Voornaam = 'Aurelius';
update Boeken
    set Categorie = concat('(1) ', Categorie);
select Categorie from Boeken;

substring

Ik krijg de vraag om '(1)' terug weg te nemen. Hiervoor is substring een goede kandidaag.

-- Hier heb je '[1]' gezet voor de tekst als er 'Wiskunde' staat.
update Boeken set Categorie = substring(Categorie, 4, len(Categorie) -3)
   where left(Categorie, 3) = '[1]';

-- Hier knip je de '[1]' er af door vanaf de 4de char te beginnen ''substring(Categorie, 4''
-- en dan de lengte daarvoor - 3 te doen ''len(Categorie) -3''
-- als er een '[1]' in de categorie staat left voor tot en met de 3de karakter.
select * from Boeken;